package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashSet;
import java.util.Set;

import javax.swing.JComboBox;
import javax.swing.table.AbstractTableModel;

import test.TestString;

public class BuyTableModel extends AbstractTableModel {
	private static final long serialVersionUID = -4973587073970933331L;
	private ArrayList<ArrayList<Object>> data;
	private ArrayList<String> columnIdentifiers;
	private Set<Object> listID;
	private Set<Object> nameDevice;
	private Set<Object> providers;
	private Set<Object> typeDevice;
	private Set<Object> units;
	private Connection connect;

	// TODO constructor
	public BuyTableModel() {
		this.data = new ArrayList<ArrayList<Object>>();
		this.columnIdentifiers = new ArrayList<String>(Arrays.asList("STT",
				"M\u00E3 h\u00E0ng", "Lo\u1EA1i h\u00E0ng",
				"T\u00EAn h\u00E0ng", "Nh\u00E0 s\u1EA3n xu\u1EA5t",
				"\u0110\u01A1n v\u1ECB t\u00EDnh",
				"B\u1EA3o h\u00E0nh (th\u00E1ng)", "S\u1ED1 l\u01B0\u1EE3ng",
				"\u0110\u01A1n gi\u00E1", "Thu\u1EBF su\u1EA5t (%)"));

		this.listID = new HashSet<>();
		this.nameDevice = new HashSet<>();
		this.providers = new HashSet<>();
		this.typeDevice = new HashSet<>();
		this.units = new HashSet<>();
		loadDataDevice();
		addNullRow();
	}

	@Override
	public boolean isCellEditable(int rowIndex, int columnIndex) {
		return (columnIndex > 0) ? true : false;
	}

	@Override
	public int getColumnCount() {
		return columnIdentifiers.size();
	}

	@Override
	public int findColumn(String columnName) {
		for (int i = 0; i < columnIdentifiers.size(); i++) {
			if (((String) columnIdentifiers.get(i))
					.equalsIgnoreCase(columnName))
				return i;
		}
		return -1;
	}

	@Override
	public int getRowCount() {
		return data.size();
	}

	@Override
	public String getColumnName(int column) {
		return columnIdentifiers.get(column);
	}

	@Override
	public Object getValueAt(int rowIndex, int columnIndex) {
		return data.get(rowIndex).get(columnIndex);
	}

	// TODO set value
	@Override
	public void setValueAt(Object aValue, int rowIndex, int columnIndex) {
		data.get(rowIndex).set(columnIndex, aValue);
		switch (columnIndex) {
		case 1: // id device
			setDataByID((String) aValue, data.get(rowIndex));
			break;
		case 2: // type
			createId((String) aValue, data.get(rowIndex), 4, 1);
			break;
		case 3: // name device
			createId((String) aValue, data.get(rowIndex), 5, 2);
			break;
		case 4: // name provider
			createId((String) aValue, data.get(rowIndex), 5, 3);
			break;
		}

		fireTableRowsUpdated(rowIndex, rowIndex);
	}

	@Override
	public Class<?> getColumnClass(int columnIndex) {
		switch (columnIndex) {
		case 0:
		case 5:
		case 6:
		case 7:
			return Integer.class;
		case 8:
		case 9:
			return Double.class;
		default:
			return String.class;
		}
		// return data.get(0).get(columnIndex).getClass();
	}

	// TODO add null row
	public void addNullRow() {
		ArrayList<Object> tmp = new ArrayList<Object>();
		for (int i = 0; i < columnIdentifiers.size(); i++) {
			tmp.add(null);
		}

		if (data.size() > 1)
			checkRow("");

		data.add(tmp);
		numberCount();
		fireTableDataChanged();
	}

	public boolean isEmpty() {
		return data.size() == 0;
	}

	// to used in delete or refresh row method
	public void clearData() {
		this.data.clear();
	}

	public void refreshData() {
		clearData();
		addNullRow();
	}

	public void deleteRow(int row) {
		this.data.remove(row);
	}

	public ArrayList<Object> getRowAt(int row) {
		return this.data.get(row);
	}

	// TODO find cell null
	public boolean checkCellNull() {
		for (int i = 0; i < data.size(); i++) { // row
			ArrayList<Object> tmp = data.get(i);
			for (int j = 0; j < tmp.size(); j++) { // column
				if (tmp.get(j) == null || tmp.get(j).equals("")) {
					return true;
				}
			}
		}
		return false;
	}

	// stt
	public void numberCount() {
		for (int i = 0; i < data.size(); i++) {
			ArrayList<Object> tmp = data.get(i);
			tmp.set(0, i + 1);
		}
	}

	// set data of all column to data of device
	public void setDataByID(String id, ArrayList<Object> aRow) {
		try {
			connect = getConnection();
			PreparedStatement pre = connect
					.prepareStatement("SELECT * FROM device WHERE id = ?");
			pre.setString(1, id);
			ResultSet rs = pre.executeQuery();

			if (rs.first()) {
				aRow.set(2, rs.getString("type"));
				aRow.set(3, rs.getString("name"));
				aRow.set(4, rs.getString("provider"));
				aRow.set(5, rs.getString("unit"));
				aRow.set(6, rs.getInt("guarantee"));
				// aRow.set(7, rs.getInt("amount"));
				aRow.set(8, rs.getDouble("sellprice"));
				aRow.set(9, rs.getDouble("tax"));
			}

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connect != null)
				try {
					connect.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
	}

	// use after add null row
	public void checkRow(String id) {
		int size = data.size();
		ArrayList<Object> list = null;
		for (int i = 0; i < size; i++) {
			list = data.get(i);
			id = (String) list.get(1);
			for (int j = i + 1; j < size; j++) {
				list = data.get(j);
				if (id.equalsIgnoreCase((String) list.get(1))) {
					megaRow(i, (int) data.get(j).get(7));
					deleteRow(j);
					size--;
				}
			}
		}
	}

	// computing sum of 2 row has same id device
	public void megaRow(int row1, int amount) {
		ArrayList<Object> tmp = data.get(row1);
		tmp.set(7, (int) tmp.get(7) + amount);
	}

	// TODO create id new device; use in column type device
	private void createId(String value, ArrayList<Object> aRow, int nChar,
			int offset) {
		if (!value.equals("")) {
			String id = (String) ((aRow.get(1) != null) ? aRow.get(1) : "");
			String text = TestString.subStringUpperCase(value, nChar);
			if (!id.startsWith(text)) {
				if (id.length() >= 14) {
					switch (offset) {
					case 1: // type device
						for (Object obj : typeDevice) {
							TypeDevice tp = (TypeDevice) obj;
							if (value.equalsIgnoreCase(tp.getName())) {
								text = tp.getId();
								break;
							}
						}
						id = id.substring(nChar);
						aRow.set(1, text.concat(id));
						break;
					case 2: // name device
						String left = id.substring(0, 4);
						String right = TestString.right(id, 5);
						text = left.concat(text);
						aRow.set(1, text.concat(right));
						break;
					case 3: // name provider
						for (Object obj : providers) {
							Provider pro = (Provider) obj;
							if (value.equalsIgnoreCase(pro.getName())) {
								text = pro.getId();
								break;
							}
						}
						id = id.substring(0, id.length() - nChar);
						aRow.set(1, id.concat(text));
						break;
					}
				} else {
					switch (offset) {
					case 1:
						for (Object obj : typeDevice) {
							TypeDevice tp = (TypeDevice) obj;
							if (value.equalsIgnoreCase(tp.getName())) {
								text = tp.getId();
								break;
							}
						}
						aRow.set(1, text.concat(id));
						break;
					case 2:
						if (aRow.get(2) == null) {
							aRow.set(1, text.concat(id));
						} else if (aRow.get(3) == null) {
							aRow.set(1, id.concat(text));
						} else {
							String left = id.substring(0, 4);
							String right = TestString.right(id, 5);
							text = left.concat(text);
							aRow.set(1, text.concat(right));
						}
						break;
					case 3:
						for (Object obj : providers) {
							Provider pro = (Provider) obj;
							if (value.equalsIgnoreCase(pro.getName())) {
								text = pro.getId();
								break;
							}
						}
						aRow.set(1, id.concat(text));
						break;
					}
				}
			}
		}
	}

	// TODO load information
	private void loadDataDevice() {
		try {
			connect = getConnection();
			Statement sta = connect.createStatement();
			ResultSet rs = null;

			// load id device
			rs = sta.executeQuery("SELECT id, name FROM device");
			while (rs.next()) {
				this.listID.add(rs.getString("id"));
				this.nameDevice.add(rs.getString("name"));
			}

			// load provider
			rs = sta.executeQuery("SELECT * FROM provider");
			while (rs.next()) {
				String id = rs.getString("id");
				String name = rs.getString("name");
				String note = rs.getString("address");
				this.providers.add(new Provider(id, name, note));
			}

			// load type device
			rs = sta.executeQuery("SELECT * FROM typedevice");
			while (rs.next()) {
				String id = rs.getString("id");
				String name = rs.getString("name");
				this.typeDevice.add(new TypeDevice(id, name));
			}

			// load unit
			rs = sta.executeQuery("SELECT unit FROM unit");
			while (rs.next()) {
				this.units.add(rs.getString(1));
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connect != null)
				try {
					connect.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
	}

	// TODO create combobox
	public void createComboBox(JComboBox<Object> cbx, int x) {
		Set<Object> tmp = null;
		switch (x) {
		case 1: // id device
			tmp = this.listID;
			break;
		case 2: // type device
			tmp = this.typeDevice;
			break;
		case 3: // name device
			tmp = this.nameDevice;
			break;
		case 4: // name provider
			tmp = this.providers;
			break;
		case 5: // unit
			tmp = this.units;
			break;
		}
		if (tmp != null)
			for (Object obj : tmp) {
				cbx.addItem(obj.toString());
			}
	}

	public void saveDeviceList(int idDetail, Date date)
			throws ClassNotFoundException, SQLException {
		connect = getConnection();
		PreparedStatement preUpdate = connect
				.prepareStatement("UPDATE device SET amount = ? WHERE id = ?");
		PreparedStatement preInsert = connect
				.prepareStatement("INSERT INTO detailbill VALUES(?,?,?)");
		PreparedStatement pre = connect
				.prepareStatement("SELECT * FROM device WHERE id = ?");
		ResultSet rs = null;

		for (int i = 0; i < data.size(); i++) {
			ArrayList<Object> row = data.get(i);

			pre.setString(1, (String) row.get(1));
			rs = pre.executeQuery();

			if (rs.first()) {
				preUpdate.setInt(1, rs.getInt("amount") + (int) row.get(7));
				preUpdate.setString(2, (String) row.get(1));
				preUpdate.executeUpdate();
			} else {
				createNewDevice(row, date);
			}

			// create detail bill
			preInsert.setInt(1, idDetail);
			preInsert.setString(2, (String) row.get(1));
			preInsert.setInt(3, (int) row.get(7));
			preInsert.executeUpdate();
		}
	}

	private void createNewDevice(ArrayList<Object> row, Date date)
			throws SQLException {
		Device d = new Device((String) row.get(1), (String) row.get(3),
				(String) row.get(2), (String) row.get(4), date,
				(double) row.get(8), 0, (int) row.get(6), (int) row.get(7));
		try {
			connect = getConnection();
			createTypeDevice(d.getId().substring(0, 4), d.getType(), connect);
			createProvider(d.getId(), d.getProvider(), connect);
			createUnit((String) row.get(4), connect);

			// create device
			PreparedStatement pre = connect
					.prepareStatement("INSERT INTO device VALUES(?,?,?,?,?,?,?,?,?,?,?)");
			pre.setString(1, d.getId());
			pre.setString(2, d.getName());
			pre.setString(3, d.getType());
			pre.setString(4, d.getProvider());
			pre.setString(5, (String) row.get(5));
			pre.setDate(6, (java.sql.Date) d.getInputDay());
			pre.setDouble(7, d.getBuyPrice());
			pre.setDouble(8, d.getSellPrice());
			pre.setInt(9, d.getGuarantee());
			pre.setInt(10, d.getAmount());
			pre.setDouble(11, (double) row.get(9));

			pre.executeUpdate();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	private void createUnit(String unit, Connection connect)
			throws SQLException {
		PreparedStatement pre = connect
				.prepareStatement("SELECT * FROM unit WHERE unit = ?");
		ResultSet rs = pre.executeQuery();

		if (!rs.first()) {
			PreparedStatement preInsert = connect
					.prepareStatement("INSERT INTO unit VALUES(?)");
			preInsert.setString(1, unit);

			preInsert.executeUpdate();
		}
	}

	private void createProvider(String id, String name, Connection connect)
			throws SQLException {
		PreparedStatement pre = connect
				.prepareStatement("SELECT * FROM provider WHERE id = ?");
		pre.setString(1, id);
		ResultSet rs = pre.executeQuery();

		if (!rs.first()) {// chua co trong csdl
			PreparedStatement preInsert = connect
					.prepareStatement("INSERT INTO provider(id, name) VALUES(?,?)");
			preInsert.setString(1, id);
			preInsert.setString(2, name);

			preInsert.executeUpdate();
		}
	}

	private void createTypeDevice(String type, String name, Connection connect)
			throws SQLException {
		PreparedStatement pre = connect
				.prepareStatement("SELECT * FROM typedevice WHERE id = ?");
		pre.setString(1, type);
		ResultSet rs = pre.executeQuery();

		if (!rs.first()) {// chua co trong csdl
			PreparedStatement preInsert = connect
					.prepareStatement("INSERT INTO typedevice VALUES(?,?)");
			pre.setString(1, type);
			pre.setString(2, name);

			preInsert.executeUpdate();
		}
	}

	// TODO save NOT USE
	public boolean saveData() {
		try {
			connect = getConnection();
			PreparedStatement preUpdate = connect
					.prepareStatement("UPDATE device SET amount = ? WHERE id = ?");
			PreparedStatement pre = connect
					.prepareStatement("SELECT * FROM device WHERE id = ?");
			ResultSet rs = null;

			for (int i = 0; i < data.size(); i++) {
				ArrayList<Object> row = data.get(i);
				pre.setString(1, (String) row.get(1));
				rs = pre.executeQuery();

				if (rs.first()) {
					preUpdate.setInt(1, rs.getInt("amount") + (int) row.get(7));
					preUpdate.setString(2, (String) row.get(1));
					preUpdate.executeUpdate();
				} else {

				}
			}
			loadDataDevice();

			return true;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connect != null) {
				try {
					connect.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return false;
	}

	private Connection getConnection() throws ClassNotFoundException,
			SQLException {
		return (connect != null && !connect.isClosed()) ? connect : Database
				.connect();
	}

}
